{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 不同产品的销售情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "from beakerx import *\n",
    "import pandasql\n",
    "pysqldf = lambda sql: pandasql.sqldf(sql, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_csv = pd.read_csv(r\"C:\\Users\\shoujunw\\Downloads\\order1.csv\") # df[\"mi\"] = df[\"mi\"].dt.strftime('%Y-%m-%d')\n",
    "\n",
    "df:DataFrame = pysqldf(\"\"\"\n",
    "select julianday(Date(ma)) - julianday(Date(mi)) as dua\n",
    "    , * \n",
    "    from df_csv\n",
    "    -- where ma -mi > 7 -- 选取持续时间>1周的产品\n",
    "\n",
    "\"\"\")\n",
    "# 给sum_amount, avg_amount_night 添加rank\n",
    "# df[\"g\"] = \"g\"\n",
    "df[\"sum_amount_rk\"] = df[\"sum_amount\"].rank(ascending=False).astype(int)\n",
    "df[\"avg_amount_night_rk\"] = df[\"avg_amount_night\"].rank(ascending=False).astype(int)\n",
    "df.to_csv(\"tmp.csv\")\n",
    "# TableDisplay(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Applications\\anaconda3\\lib\\site-packages\\beakerx\\tabledisplay\\tabledisplay.py:109: FutureWarning: The 'get_values' method is deprecated and will be removed in a future version. Use '.to_numpy()' or '.array' instead.\n",
      "  value = args[0][columnName].get_values()[index]\n",
      "D:\\Applications\\anaconda3\\lib\\site-packages\\beakerx\\tabledisplay\\tabledisplay.py:114: FutureWarning: The 'get_values' method is deprecated and will be removed in a future version. Use '.to_numpy()' or '.array' instead.\n",
      "  index_values = args[0].index.get_values()[index]\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6b551e9187e84988b2288f3280a57673",
       "version_major": 2,
       "version_minor": 0
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "tmp_df = pd.read_csv(\"tmp.csv\", index_col=0)\n",
    "TableDisplay(tmp_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 如何权衡 量多 间夜gmv小   和 量少间夜gmv多的 比较\n",
    "1. 产品: 别栋别墅: 每间夜8542价格最高, 所贡献的总体gmv: 排名275名: 17084\n",
    "        新房特惠: 每间夜价格小308, 订单量多贡献总体gmv: 5亿, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>g</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>g</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>g</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.3</td>\n",
       "      <td>g</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a  g\n",
       "0  0.2  g\n",
       "1  0.1  g\n",
       "2  0.3  g"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdf = pd.DataFrame([[0.2], [0.1], [0.3]], columns=[\"a\"])\n",
    "newdf[\"g\"] = \"g\"\n",
    "newdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2\n",
       "1    1\n",
       "2    3\n",
       "Name: a, dtype: int32"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdf.groupby(\"g\")[\"a\"].rank().astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 活动持续时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dua</th>\n",
       "      <th>product_name</th>\n",
       "      <th>mi</th>\n",
       "      <th>ma</th>\n",
       "      <th>sum_amount</th>\n",
       "      <th>avg_amount</th>\n",
       "      <th>max_amount</th>\n",
       "      <th>min_amount</th>\n",
       "      <th>avg_amount_night</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.5m大床房(内宾)(PP)(预付)</td>\n",
       "      <td>2019-10-31</td>\n",
       "      <td>2019-10-31</td>\n",
       "      <td>144.000000</td>\n",
       "      <td>144.000000</td>\n",
       "      <td>144.000000</td>\n",
       "      <td>144.000000</td>\n",
       "      <td>144.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>201双床间</td>\n",
       "      <td>2019-10-26</td>\n",
       "      <td>2020-05-02</td>\n",
       "      <td>1004.000000</td>\n",
       "      <td>334.666667</td>\n",
       "      <td>438.000000</td>\n",
       "      <td>283.000000</td>\n",
       "      <td>334.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4人间(床位)</td>\n",
       "      <td>2018-09-29</td>\n",
       "      <td>2018-09-29</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>115.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>7日起租舱位房(内宾)</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>2018-09-25</td>\n",
       "      <td>472.000000</td>\n",
       "      <td>67.428571</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>67.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>8人宿舍(床位房)(混合入住)</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>212.000000</td>\n",
       "      <td>212.000000</td>\n",
       "      <td>212.000000</td>\n",
       "      <td>212.000000</td>\n",
       "      <td>106.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9995</td>\n",
       "      <td>0</td>\n",
       "      <td>素洁两室一厅温馨居</td>\n",
       "      <td>2019-07-27</td>\n",
       "      <td>2019-10-04</td>\n",
       "      <td>969.619995</td>\n",
       "      <td>323.206665</td>\n",
       "      <td>496.000000</td>\n",
       "      <td>235.619995</td>\n",
       "      <td>240.539998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9996</td>\n",
       "      <td>0</td>\n",
       "      <td>绅士六人间(一张床位)(预付)</td>\n",
       "      <td>2019-02-06</td>\n",
       "      <td>2019-04-19</td>\n",
       "      <td>214.590004</td>\n",
       "      <td>71.530001</td>\n",
       "      <td>80.370003</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>71.530001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9997</td>\n",
       "      <td>0</td>\n",
       "      <td>经济复式大床房(PP)(预付)</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>804.000000</td>\n",
       "      <td>402.000000</td>\n",
       "      <td>536.000000</td>\n",
       "      <td>268.000000</td>\n",
       "      <td>268.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9998</td>\n",
       "      <td>0</td>\n",
       "      <td>绿影繁花-大床房(限时促销)</td>\n",
       "      <td>2018-06-07</td>\n",
       "      <td>2018-08-25</td>\n",
       "      <td>443.000000</td>\n",
       "      <td>147.666667</td>\n",
       "      <td>163.000000</td>\n",
       "      <td>127.000000</td>\n",
       "      <td>147.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9999</td>\n",
       "      <td>0</td>\n",
       "      <td>绿野仙踪大床房(公卫)(PP)(预付)</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>2018-11-15</td>\n",
       "      <td>226.000000</td>\n",
       "      <td>113.000000</td>\n",
       "      <td>113.000000</td>\n",
       "      <td>113.000000</td>\n",
       "      <td>113.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10000 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      dua         product_name          mi          ma   sum_amount  \\\n",
       "0       0  1.5m大床房(内宾)(PP)(预付)  2019-10-31  2019-10-31   144.000000   \n",
       "1      -1               201双床间  2019-10-26  2020-05-02  1004.000000   \n",
       "2       0              4人间(床位)  2018-09-29  2018-09-29   115.000000   \n",
       "3       0          7日起租舱位房(内宾)  2018-08-08  2018-09-25   472.000000   \n",
       "4       0      8人宿舍(床位房)(混合入住)  2020-01-02  2020-01-02   212.000000   \n",
       "...   ...                  ...         ...         ...          ...   \n",
       "9995    0            素洁两室一厅温馨居  2019-07-27  2019-10-04   969.619995   \n",
       "9996    0      绅士六人间(一张床位)(预付)  2019-02-06  2019-04-19   214.590004   \n",
       "9997    0      经济复式大床房(PP)(预付)  2019-05-01  2019-05-08   804.000000   \n",
       "9998    0       绿影繁花-大床房(限时促销)  2018-06-07  2018-08-25   443.000000   \n",
       "9999    0  绿野仙踪大床房(公卫)(PP)(预付)  2018-11-13  2018-11-15   226.000000   \n",
       "\n",
       "      avg_amount  max_amount  min_amount  avg_amount_night  \n",
       "0     144.000000  144.000000  144.000000        144.000000  \n",
       "1     334.666667  438.000000  283.000000        334.666667  \n",
       "2     115.000000  115.000000  115.000000        115.000000  \n",
       "3      67.428571   80.000000   60.000000         67.428571  \n",
       "4     212.000000  212.000000  212.000000        106.000000  \n",
       "...          ...         ...         ...               ...  \n",
       "9995  323.206665  496.000000  235.619995        240.539998  \n",
       "9996   71.530001   80.370003   57.000000         71.530001  \n",
       "9997  402.000000  536.000000  268.000000        268.000000  \n",
       "9998  147.666667  163.000000  127.000000        147.666667  \n",
       "9999  113.000000  113.000000  113.000000        113.000000  \n",
       "\n",
       "[10000 rows x 9 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 0-6天"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "encoding": "# -*- coding: utf-8 -*-",
   "formats": "ipynb,py:light"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": false,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
